Broadband Outage Detection

Mohammed Dadapeer[3532] Batch : 78 INSOFE Bangalore

Problem Description:

India is seeing an explosion of new competitors in the Broadband space. 'India Broadband' is a company that is now seeing a lot of customer churn due to customer dissatisfaction because of broadband outages.The company has now curated a dataset, where it tracks several variables that it believes impact the outage_duration. They have tracked three different outage durations, 0 for no outage, 1 for short outages that last anywhere between a few minutes and a maximum of 2 hours, and 2 for long outages that can last from 2 hours to sometimes even a couple of days.You will now have to use these metrics that the company has tracked to create a machine learning model that will be able to predict the outage_duration so that the company can better handle outages and improve customer satisfaction and therefore reduce customer churn.

Exploratory Data Analysis

Importing the required libararies

In [1]:
import pandas as pd
import numpy as np

from sklearn import preprocessing
from sklearn.naive_bayes import GaussianNB
from sklearn.decomposition import PCA
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import accuracy_score,classification_report


from sklearn.preprocessing import StandardScaler
from sklearn.preprocessing import OneHotEncoder
from sklearn.model_selection import train_test_split
from sklearn.pipeline import Pipeline
from sklearn.model_selection import GridSearchCV, RandomizedSearchCV

from sklearn.svm import SVC
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import confusion_matrix

import warnings
warnings.filterwarnings('ignore')
from sklearn.naive_bayes import GaussianNB
from sklearn import tree
from sklearn import svm
from sklearn.ensemble import AdaBoostClassifier

#!pip install plotly
import plotly.offline as pyo
import plotly.graph_objs as go
from scipy.stats.mstats import mode
from sklearn.preprocessing import LabelEncoder

%matplotlib inline
from matplotlib.pylab import rcParams
rcParams['figure.figsize'] = 12, 4
C:\Users\admin\Anaconda3\lib\site-packages\sklearn\ensemble\weight_boosting.py:29: DeprecationWarning: numpy.core.umath_tests is an internal NumPy module and should not be imported. It will be removed in a future NumPy release.
  from numpy.core.umath_tests import inner1d

Importing Data sets

In [2]:
train_data = pd.read_csv('train_data.csv') 
test_data = pd.read_csv('test_data.csv')
server_data = pd.read_csv('server_data.csv')
report_data = pd.read_csv('report_data.csv')
broadband_data = pd.read_csv('broadband_data.csv')
outage_data = pd.read_csv('outage_data.csv')

Checking the shape of all Data sets

In [3]:
print('The shape of broadband_data is: {}\n'.format(broadband_data.shape))
print('The shape of outage_data is: {}\n'.format(outage_data.shape))
print('The shape of report_data is: {}\n'.format(report_data.shape))
print('The shape of server_data is: {}\n'.format(server_data.shape))
print('The shape of train_data is: {}'.format(train_data.shape))
print('The shape of test_data is: {}'.format(test_data.shape))
The shape of broadband_data is: (21076, 2)

The shape of outage_data is: (18552, 2)

The shape of report_data is: (58671, 3)

The shape of server_data is: (31170, 2)

The shape of train_data is: (5904, 3)
The shape of test_data is: (1477, 2)

Checking the unique columes in the Data sets

In [4]:
print(outage_data.nunique())
print('\n')
print(train_data.nunique())
print('\n')
print(broadband_data.nunique())
print('\n')
print(server_data.nunique())
print('\n')
print(report_data.nunique())
print('\n')
print(test_data.nunique())
print('\n')
id             18552
outage_type        5
dtype: int64


id                 5904
area_code           876
outage_duration       3
dtype: int64


id                18552
broadband_type       10
dtype: int64


id                     18552
transit_server_type       53
dtype: int64


id                 18552
log_report_type      386
volume               341
dtype: int64


id           1477
area_code     531
dtype: int64


In [5]:
## Checking the head of all Data sets
print(train_data.head())
print('\n')
print(server_data.head())
print('\n')
print(report_data.head())
print('\n')
print(broadband_data.head())
print('\n')
print(outage_data.head())
print('\n')
print(test_data.head())
print('\n')
      id area_code  outage_duration
0  13366  area_415                1
1   6783  area_474                0
2   9519  area_931                1
3  10202  area_700                1
4   4555  area_600                2


     id     transit_server_type
0  6597  transit_server_type_11
1  8011  transit_server_type_15
2  2597  transit_server_type_15
3  5022  transit_server_type_15
4  5022  transit_server_type_11


     id      log_report_type  volume
0  6597   log_report_type_68       6
1  8011   log_report_type_68       7
2  2597   log_report_type_68       1
3  5022  log_report_type_172       2
4  5022   log_report_type_56       1


     id    broadband_type
0  6597  broadband_type_8
1  8011  broadband_type_8
2  2597  broadband_type_8
3  5022  broadband_type_8
4  6852  broadband_type_8


     id    outage_type
0  6597  outage_type_2
1  8011  outage_type_2
2  2597  outage_type_2
3  5022  outage_type_1
4  6852  outage_type_1


      id area_code
0   3340  area_344
1  14067  area_933
2   1134   area_16
3     27  area_793
4   9793  area_344


Checking the outage duration

In [6]:
## outage duration in train_data
train_data['outage_duration'].value_counts()
Out[6]:
0    3827
1    1496
2     581
Name: outage_duration, dtype: int64
In [7]:
## plot for ouatge duration
import seaborn as sns
sns.set_style("whitegrid")
import matplotlib.pyplot as plt

plt.figure(figsize = (14,6))
sns.countplot(train_data['outage_duration'])
plt.tight_layout()
plt.show()
In [8]:
val=list(train_data['outage_duration'].value_counts())
for i in range(len(val)):
    print(train_data['outage_duration'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
0 65 %
1 25 %
2 10 %

As there are three different Outage durations

  • outage durations, 0 for no outage
  • outage durations, 1 for short outages that last anywhere between a few minutes and a maximum of 2 hours
  • outage durations, 2 for long outages that can last from 2 hours to sometimes even a couple of days.

Plot with ID and Outage Durations.

In [9]:
data = [go.Scatter(x=train_data.loc[train_data.outage_duration==0,'area_code'],
                 y=train_data.loc[train_data.outage_duration==0,'id'],
                 mode="markers",text=train_data["outage_duration"]),
        go.Scatter(x=train_data.loc[train_data.outage_duration==1,'area_code'],
                 y=train_data.loc[train_data.outage_duration==1,'id'],
                 mode="markers",text=train_data["outage_duration"]),
       go.Scatter(x=train_data.loc[train_data.outage_duration==2,'area_code'],
                 y=train_data.loc[train_data.outage_duration==2,'id'],
                 mode="markers",text=train_data["outage_duration"])]
layout=go.Layout(title="ID vs Area Code",xaxis={"title":"area_code"},yaxis=dict(title="ID"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()

Preprocessing

Checking all the inputs from data sets

Broadband_data

In [10]:
broadband_data.head()
Out[10]:
id broadband_type
0 6597 broadband_type_8
1 8011 broadband_type_8
2 2597 broadband_type_8
3 5022 broadband_type_8
4 6852 broadband_type_8
In [11]:
broadband_data.shape
Out[11]:
(21076, 2)
In [12]:
broadband_data['broadband_type'].value_counts()
Out[12]:
broadband_type_8     10268
broadband_type_2      8918
broadband_type_6       582
broadband_type_7       498
broadband_type_4       330
broadband_type_9       190
broadband_type_3       145
broadband_type_10       73
broadband_type_1        58
broadband_type_5        14
Name: broadband_type, dtype: int64
In [13]:
##Percentage of Broadband types through Value counts in the Data
val=list(broadband_data['broadband_type'].value_counts())
for i in range(len(val)):
    print(broadband_data['broadband_type'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
broadband_type_8 49 %
broadband_type_2 42 %
broadband_type_6 3 %
broadband_type_7 2 %
broadband_type_4 2 %
broadband_type_9 1 %
broadband_type_3 1 %
broadband_type_10 0 %
broadband_type_1 0 %
broadband_type_5 0 %
In [14]:
#count plot for broadband type

plt.figure(figsize = (14,6))
sns.countplot(broadband_data['broadband_type'])
plt.tight_layout()
plt.show()

outage_data

In [15]:
outage_data.head()
Out[15]:
id outage_type
0 6597 outage_type_2
1 8011 outage_type_2
2 2597 outage_type_2
3 5022 outage_type_1
4 6852 outage_type_1
In [16]:
outage_data.shape
Out[16]:
(18552, 2)
In [17]:
outage_data['outage_type'].value_counts()
Out[17]:
outage_type_2    8737
outage_type_1    8728
outage_type_4    1014
outage_type_5      65
outage_type_3       8
Name: outage_type, dtype: int64
In [18]:
#count plot for Outage Duration

plt.figure(figsize = (14,6))
sns.countplot(outage_data['outage_type'])
plt.tight_layout()
plt.show()
In [19]:
## outage_type in percentage
val=list(outage_data['outage_type'].value_counts())
for i in range(len(val)):
    print(outage_data['outage_type'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
outage_type_2 47 %
outage_type_1 47 %
outage_type_4 5 %
outage_type_5 0 %
outage_type_3 0 %

report_data

In [20]:
report_data.head()
Out[20]:
id log_report_type volume
0 6597 log_report_type_68 6
1 8011 log_report_type_68 7
2 2597 log_report_type_68 1
3 5022 log_report_type_172 2
4 5022 log_report_type_56 1
In [21]:
report_data.shape
Out[21]:
(58671, 3)
In [22]:
report_data.nunique()
Out[22]:
id                 18552
log_report_type      386
volume               341
dtype: int64
In [23]:
log_report_ = report_data['log_report_type'].value_counts()
In [24]:
log_report_.head(10)
Out[24]:
log_report_type_312    5267
log_report_type_232    4754
log_report_type_82     3472
log_report_type_203    2823
log_report_type_313    2145
log_report_type_233    1901
log_report_type_307    1597
log_report_type_54     1573
log_report_type_170    1526
log_report_type_71     1514
Name: log_report_type, dtype: int64
In [25]:
#count plot for Log Report Type

plt.figure(figsize = (30,8))
sns.countplot(report_data['log_report_type'].value_counts())
plt.tight_layout()
plt.show()

server_data

In [26]:
server_data.shape
Out[26]:
(31170, 2)
In [27]:
server_data.head()
Out[27]:
id transit_server_type
0 6597 transit_server_type_11
1 8011 transit_server_type_15
2 2597 transit_server_type_15
3 5022 transit_server_type_15
4 5022 transit_server_type_11
In [28]:
server_data.nunique().value_counts()
Out[28]:
53       1
18552    1
dtype: int64
In [29]:
server_count = server_data['transit_server_type'].value_counts()
server_count
Out[29]:
transit_server_type_11    7888
transit_server_type_35    6615
transit_server_type_34    5927
transit_server_type_15    4395
transit_server_type_20    1458
transit_server_type_54     684
transit_server_type_13     582
transit_server_type_42     478
transit_server_type_44     466
transit_server_type_23     429
transit_server_type_14     330
transit_server_type_43     306
transit_server_type_22     223
transit_server_type_50     154
transit_server_type_10     145
transit_server_type_21     136
transit_server_type_18      73
transit_server_type_47      69
transit_server_type_26      65
transit_server_type_32      63
transit_server_type_30      60
transit_server_type_45      53
transit_server_type_24      46
transit_server_type_27      44
transit_server_type_29      42
transit_server_type_40      40
transit_server_type_46      38
transit_server_type_2       37
transit_server_type_28      32
transit_server_type_8       29
transit_server_type_6       28
transit_server_type_5       26
transit_server_type_7       24
transit_server_type_38      19
transit_server_type_3       19
transit_server_type_36      18
transit_server_type_39      18
transit_server_type_53      17
transit_server_type_49      17
transit_server_type_9       14
transit_server_type_19      14
transit_server_type_37      10
transit_server_type_31      10
transit_server_type_12       6
transit_server_type_25       5
transit_server_type_1        4
transit_server_type_51       4
transit_server_type_17       3
transit_server_type_41       2
transit_server_type_48       2
transit_server_type_52       1
transit_server_type_33       1
transit_server_type_4        1
Name: transit_server_type, dtype: int64
In [30]:
#plotting with ID vs transit server type
# x and y given as array_like objects
import plotly.express as px
fig = px.scatter(server_data['id'], server_data['transit_server_type'])
fig.show()
In [31]:
server_count.head()
Out[31]:
transit_server_type_11    7888
transit_server_type_35    6615
transit_server_type_34    5927
transit_server_type_15    4395
transit_server_type_20    1458
Name: transit_server_type, dtype: int64
In [32]:
# plotting server typr count
#count plot for transit server type

plt.figure(figsize = (32,12))
sns.countplot(server_data['transit_server_type'])
plt.tight_layout()
plt.show()

Merging all the data sets with train_data

Merging broadband_data with train_data

In [33]:
train_broadband_merge = pd.merge(train_data,broadband_data,left_on = ['id'],
                               right_on = ['id'],how='left')
In [34]:
train_broadband_merge.head()
Out[34]:
id area_code outage_duration broadband_type
0 13366 area_415 1 broadband_type_2
1 6783 area_474 0 broadband_type_2
2 9519 area_931 1 broadband_type_8
3 10202 area_700 1 broadband_type_8
4 4555 area_600 2 broadband_type_8
In [35]:
train_broadband_merge.shape
Out[35]:
(6754, 4)
In [36]:
##ploting the data sets to check the inputs with train data
broad_band=train_broadband_merge.broadband_type.value_counts()
broad_band.head(10).plot(kind="bar",figsize=(10,10))
broad_band.head(10)
Out[36]:
broadband_type_8     3246
broadband_type_2     2851
broadband_type_6      201
broadband_type_7      179
broadband_type_4      114
broadband_type_9       61
broadband_type_3       44
broadband_type_10      28
broadband_type_1       27
broadband_type_5        3
Name: broadband_type, dtype: int64

Merging server_data with train_data

In [37]:
train_server_merge = pd.merge(train_data,server_data,left_on = ['id'],
                               right_on = ['id'],how='left')
In [38]:
train_server_merge.head()
Out[38]:
id area_code outage_duration transit_server_type
0 13366 area_415 1 transit_server_type_35
1 6783 area_474 0 transit_server_type_35
2 6783 area_474 0 transit_server_type_34
3 9519 area_931 1 transit_server_type_15
4 10202 area_700 1 transit_server_type_11
In [39]:
train_server_merge.shape
Out[39]:
(9968, 4)
In [40]:
# plotting server type and outage type
server_outage=train_server_merge.transit_server_type.value_counts()
server_outage.head(10)
server_outage.head(10).plot(kind="bar",figsize=(10,10))
Out[40]:
<matplotlib.axes._subplots.AxesSubplot at 0x1e4dc302da0>

Merging outage_data with train_data

In [41]:
train_outage_merge = pd.merge(train_data,outage_data,left_on = ['id'],
                               right_on = ['id'],how='left')
In [42]:
train_outage_merge.head()
Out[42]:
id area_code outage_duration outage_type
0 13366 area_415 1 outage_type_4
1 6783 area_474 0 outage_type_2
2 9519 area_931 1 outage_type_2
3 10202 area_700 1 outage_type_1
4 4555 area_600 2 outage_type_2
In [43]:
train_outage_merge.shape
Out[43]:
(5904, 4)
In [44]:
# plotting area type and outage duration
outage_duration=train_outage_merge.area_code.value_counts()
outage_duration.head(10)
outage_duration.head(10).plot(kind="bar",figsize=(10,10))
Out[44]:
<matplotlib.axes._subplots.AxesSubplot at 0x1e4dc3817b8>

Merging report_data with train_data

In [45]:
train_report_merge = pd.merge(train_data,report_data,left_on = ['id'],
                               right_on = ['id'],how='left')
In [46]:
train_report_merge.head()
Out[46]:
id area_code outage_duration log_report_type volume
0 13366 area_415 1 log_report_type_312 1
1 6783 area_474 0 log_report_type_312 2
2 6783 area_474 0 log_report_type_233 1
3 6783 area_474 0 log_report_type_232 1
4 6783 area_474 0 log_report_type_315 2
In [47]:
train_report_merge.shape
Out[47]:
(18995, 5)
In [48]:
# plotting log_report_type and outage duration
report_outage=train_report_merge.log_report_type.value_counts()
report_outage.head(10)
report_outage.head(10).plot(kind="bar",figsize=(10,10))
Out[48]:
<matplotlib.axes._subplots.AxesSubplot at 0x1e4dc3fd748>

Feature engineering using train and test_data

In [49]:
## Adding a column in train_data
train_data['train'] = 'yes'
test_data['train'] = 'no'
In [50]:
# Checking the head of train and test_data
print(train_data.head())
print('\n')
print(test_data.head())
print('\n')
      id area_code  outage_duration train
0  13366  area_415                1   yes
1   6783  area_474                0   yes
2   9519  area_931                1   yes
3  10202  area_700                1   yes
4   4555  area_600                2   yes


      id area_code train
0   3340  area_344    no
1  14067  area_933    no
2   1134   area_16    no
3     27  area_793    no
4   9793  area_344    no


merging the train_data and test_data

In [51]:
temp_data = pd.concat([train_data,test_data], ignore_index=True)
In [52]:
# Checking the train and test_data shape after merge
temp_data.shape
Out[52]:
(7381, 4)
In [53]:
temp_data.head()
Out[53]:
area_code id outage_duration train
0 area_415 13366 1.0 yes
1 area_474 6783 0.0 yes
2 area_931 9519 1.0 yes
3 area_700 10202 1.0 yes
4 area_600 4555 2.0 yes
In [54]:
temp_data.tail()
Out[54]:
area_code id outage_duration train
7376 area_124 7975 NaN no
7377 area_821 10664 NaN no
7378 area_476 7753 NaN no
7379 area_38 989 NaN no
7380 area_1067 3129 NaN no
In [55]:
## arranging the columns  
data_columns=["id","area_code","train","outage_duration"]
data_columns
Out[55]:
['id', 'area_code', 'train', 'outage_duration']
In [56]:
data = temp_data[data_columns]
In [57]:
data.head()
Out[57]:
id area_code train outage_duration
0 13366 area_415 yes 1.0
1 6783 area_474 yes 0.0
2 9519 area_931 yes 1.0
3 10202 area_700 yes 1.0
4 4555 area_600 yes 2.0

Merging trian_test_data with server_data

In [58]:
server_data=server_data.merge(data,on='id',how='left')
In [59]:
## Checking the top rows of the server data after merging with train and test data
server_data.head()
Out[59]:
id transit_server_type area_code train outage_duration
0 6597 transit_server_type_11 NaN NaN NaN
1 8011 transit_server_type_15 area_1 yes 0.0
2 2597 transit_server_type_15 NaN NaN NaN
3 5022 transit_server_type_15 NaN NaN NaN
4 5022 transit_server_type_11 NaN NaN NaN
In [60]:
server_data_temp=pd.DataFrame(server_data['transit_server_type'].value_counts())
In [61]:
## checking the top most transit_server_type
server_data_temp.head()
Out[61]:
transit_server_type
transit_server_type_11 7888
transit_server_type_35 6615
transit_server_type_34 5927
transit_server_type_15 4395
transit_server_type_20 1458
In [62]:
server_data_temp.nunique()
Out[62]:
transit_server_type    44
dtype: int64
In [63]:
## checking how percentage of transit_server_type involved in the train_data
server_data_temp['Perc_Train'] = server_data.pivot_table(values='train',index='transit_server_type',aggfunc=lambda x: sum(x=='yes')/float(len(x)))
server_data_temp.head()
Out[63]:
transit_server_type Perc_Train
transit_server_type_11 7888 0.313514
transit_server_type_35 6615 0.326228
transit_server_type_34 5927 0.324785
transit_server_type_15 4395 0.311718
transit_server_type_20 1458 0.299726
In [64]:
server_data_temp
Out[64]:
transit_server_type Perc_Train
transit_server_type_11 7888 0.313514
transit_server_type_35 6615 0.326228
transit_server_type_34 5927 0.324785
transit_server_type_15 4395 0.311718
transit_server_type_20 1458 0.299726
transit_server_type_54 684 0.296784
transit_server_type_13 582 0.345361
transit_server_type_42 478 0.309623
transit_server_type_44 466 0.304721
transit_server_type_23 429 0.370629
transit_server_type_14 330 0.345455
transit_server_type_43 306 0.323529
transit_server_type_22 223 0.336323
transit_server_type_50 154 0.240260
transit_server_type_10 145 0.303448
transit_server_type_21 136 0.367647
transit_server_type_18 73 0.383562
transit_server_type_47 69 0.347826
transit_server_type_26 65 0.338462
transit_server_type_32 63 0.333333
transit_server_type_30 60 0.383333
transit_server_type_45 53 0.339623
transit_server_type_24 46 0.369565
transit_server_type_27 44 0.250000
transit_server_type_29 42 0.333333
transit_server_type_40 40 0.300000
transit_server_type_46 38 0.421053
transit_server_type_2 37 0.351351
transit_server_type_28 32 0.375000
transit_server_type_8 29 0.241379
transit_server_type_6 28 0.428571
transit_server_type_5 26 0.500000
transit_server_type_7 24 0.541667
transit_server_type_38 19 0.421053
transit_server_type_3 19 0.315789
transit_server_type_36 18 0.388889
transit_server_type_39 18 0.277778
transit_server_type_53 17 0.235294
transit_server_type_49 17 0.294118
transit_server_type_9 14 0.214286
transit_server_type_19 14 0.214286
transit_server_type_37 10 0.400000
transit_server_type_31 10 0.300000
transit_server_type_12 6 0.333333
transit_server_type_25 5 0.200000
transit_server_type_1 4 0.250000
transit_server_type_51 4 0.750000
transit_server_type_17 3 0.000000
transit_server_type_41 2 0.500000
transit_server_type_48 2 0.500000
transit_server_type_52 1 0.000000
transit_server_type_33 1 0.000000
transit_server_type_4 1 0.000000
In [65]:
server_data_temp['Mode_outage_duration'] = server_data.loc[server_data['train']=='yes'].pivot_table(values='outage_duration',index='transit_server_type', aggfunc=lambda x: mode(x).mode[0])
In [66]:
server_data_temp.iloc[-10:]
Out[66]:
transit_server_type Perc_Train Mode_outage_duration
transit_server_type_12 6 0.333333 0.0
transit_server_type_25 5 0.200000 0.0
transit_server_type_1 4 0.250000 0.0
transit_server_type_51 4 0.750000 0.0
transit_server_type_17 3 0.000000 NaN
transit_server_type_41 2 0.500000 0.0
transit_server_type_48 2 0.500000 0.0
transit_server_type_52 1 0.000000 NaN
transit_server_type_33 1 0.000000 NaN
transit_server_type_4 1 0.000000 NaN
In [67]:
server_data_temp.nunique()
Out[67]:
transit_server_type     44
Perc_Train              42
Mode_outage_duration     3
dtype: int64
In [68]:
server_data_temp['preprocess'] = server_data_temp.index.values
In [69]:
bottomlimit = 30
server_data_temp['preprocess'].iloc[bottomlimit:] = server_data_temp['Mode_outage_duration'].iloc[bottomlimit:].apply(lambda x: 'Remove' if pd.isnull(x) else 'transit_server_other_%d'%int(x))
In [70]:
server_data_temp['preprocess'].iloc[33:]
Out[70]:
transit_server_type_38    transit_server_other_0
transit_server_type_3     transit_server_other_2
transit_server_type_36    transit_server_other_0
transit_server_type_39    transit_server_other_1
transit_server_type_53    transit_server_other_2
transit_server_type_49    transit_server_other_2
transit_server_type_9     transit_server_other_0
transit_server_type_19    transit_server_other_2
transit_server_type_37    transit_server_other_0
transit_server_type_31    transit_server_other_0
transit_server_type_12    transit_server_other_0
transit_server_type_25    transit_server_other_0
transit_server_type_1     transit_server_other_0
transit_server_type_51    transit_server_other_0
transit_server_type_17                    Remove
transit_server_type_41    transit_server_other_0
transit_server_type_48    transit_server_other_0
transit_server_type_52                    Remove
transit_server_type_33                    Remove
transit_server_type_4                     Remove
Name: preprocess, dtype: object
In [71]:
print (server_data_temp['preprocess'].value_counts())
print (server_data_temp)
transit_server_other_0    12
transit_server_other_2     5
Remove                     4
transit_server_other_1     2
transit_server_type_24     1
transit_server_type_2      1
transit_server_type_34     1
transit_server_type_30     1
transit_server_type_21     1
transit_server_type_11     1
transit_server_type_8      1
transit_server_type_50     1
transit_server_type_13     1
transit_server_type_18     1
transit_server_type_54     1
transit_server_type_32     1
transit_server_type_29     1
transit_server_type_40     1
transit_server_type_10     1
transit_server_type_14     1
transit_server_type_46     1
transit_server_type_15     1
transit_server_type_47     1
transit_server_type_43     1
transit_server_type_20     1
transit_server_type_35     1
transit_server_type_45     1
transit_server_type_23     1
transit_server_type_22     1
transit_server_type_27     1
transit_server_type_44     1
transit_server_type_28     1
transit_server_type_42     1
transit_server_type_26     1
Name: preprocess, dtype: int64
                        transit_server_type  Perc_Train  Mode_outage_duration  \
transit_server_type_11                 7888    0.313514                   0.0   
transit_server_type_35                 6615    0.326228                   0.0   
transit_server_type_34                 5927    0.324785                   0.0   
transit_server_type_15                 4395    0.311718                   0.0   
transit_server_type_20                 1458    0.299726                   0.0   
transit_server_type_54                  684    0.296784                   1.0   
transit_server_type_13                  582    0.345361                   1.0   
transit_server_type_42                  478    0.309623                   0.0   
transit_server_type_44                  466    0.304721                   0.0   
transit_server_type_23                  429    0.370629                   0.0   
transit_server_type_14                  330    0.345455                   0.0   
transit_server_type_43                  306    0.323529                   0.0   
transit_server_type_22                  223    0.336323                   1.0   
transit_server_type_50                  154    0.240260                   0.0   
transit_server_type_10                  145    0.303448                   1.0   
transit_server_type_21                  136    0.367647                   0.0   
transit_server_type_18                   73    0.383562                   0.0   
transit_server_type_47                   69    0.347826                   0.0   
transit_server_type_26                   65    0.338462                   0.0   
transit_server_type_32                   63    0.333333                   0.0   
transit_server_type_30                   60    0.383333                   1.0   
transit_server_type_45                   53    0.339623                   0.0   
transit_server_type_24                   46    0.369565                   0.0   
transit_server_type_27                   44    0.250000                   0.0   
transit_server_type_29                   42    0.333333                   1.0   
transit_server_type_40                   40    0.300000                   0.0   
transit_server_type_46                   38    0.421053                   0.0   
transit_server_type_2                    37    0.351351                   1.0   
transit_server_type_28                   32    0.375000                   1.0   
transit_server_type_8                    29    0.241379                   0.0   
transit_server_type_6                    28    0.428571                   0.0   
transit_server_type_5                    26    0.500000                   2.0   
transit_server_type_7                    24    0.541667                   1.0   
transit_server_type_38                   19    0.421053                   0.0   
transit_server_type_3                    19    0.315789                   2.0   
transit_server_type_36                   18    0.388889                   0.0   
transit_server_type_39                   18    0.277778                   1.0   
transit_server_type_53                   17    0.235294                   2.0   
transit_server_type_49                   17    0.294118                   2.0   
transit_server_type_9                    14    0.214286                   0.0   
transit_server_type_19                   14    0.214286                   2.0   
transit_server_type_37                   10    0.400000                   0.0   
transit_server_type_31                   10    0.300000                   0.0   
transit_server_type_12                    6    0.333333                   0.0   
transit_server_type_25                    5    0.200000                   0.0   
transit_server_type_1                     4    0.250000                   0.0   
transit_server_type_51                    4    0.750000                   0.0   
transit_server_type_17                    3    0.000000                   NaN   
transit_server_type_41                    2    0.500000                   0.0   
transit_server_type_48                    2    0.500000                   0.0   
transit_server_type_52                    1    0.000000                   NaN   
transit_server_type_33                    1    0.000000                   NaN   
transit_server_type_4                     1    0.000000                   NaN   

                                    preprocess  
transit_server_type_11  transit_server_type_11  
transit_server_type_35  transit_server_type_35  
transit_server_type_34  transit_server_type_34  
transit_server_type_15  transit_server_type_15  
transit_server_type_20  transit_server_type_20  
transit_server_type_54  transit_server_type_54  
transit_server_type_13  transit_server_type_13  
transit_server_type_42  transit_server_type_42  
transit_server_type_44  transit_server_type_44  
transit_server_type_23  transit_server_type_23  
transit_server_type_14  transit_server_type_14  
transit_server_type_43  transit_server_type_43  
transit_server_type_22  transit_server_type_22  
transit_server_type_50  transit_server_type_50  
transit_server_type_10  transit_server_type_10  
transit_server_type_21  transit_server_type_21  
transit_server_type_18  transit_server_type_18  
transit_server_type_47  transit_server_type_47  
transit_server_type_26  transit_server_type_26  
transit_server_type_32  transit_server_type_32  
transit_server_type_30  transit_server_type_30  
transit_server_type_45  transit_server_type_45  
transit_server_type_24  transit_server_type_24  
transit_server_type_27  transit_server_type_27  
transit_server_type_29  transit_server_type_29  
transit_server_type_40  transit_server_type_40  
transit_server_type_46  transit_server_type_46  
transit_server_type_2    transit_server_type_2  
transit_server_type_28  transit_server_type_28  
transit_server_type_8    transit_server_type_8  
transit_server_type_6   transit_server_other_0  
transit_server_type_5   transit_server_other_2  
transit_server_type_7   transit_server_other_1  
transit_server_type_38  transit_server_other_0  
transit_server_type_3   transit_server_other_2  
transit_server_type_36  transit_server_other_0  
transit_server_type_39  transit_server_other_1  
transit_server_type_53  transit_server_other_2  
transit_server_type_49  transit_server_other_2  
transit_server_type_9   transit_server_other_0  
transit_server_type_19  transit_server_other_2  
transit_server_type_37  transit_server_other_0  
transit_server_type_31  transit_server_other_0  
transit_server_type_12  transit_server_other_0  
transit_server_type_25  transit_server_other_0  
transit_server_type_1   transit_server_other_0  
transit_server_type_51  transit_server_other_0  
transit_server_type_17                  Remove  
transit_server_type_41  transit_server_other_0  
transit_server_type_48  transit_server_other_0  
transit_server_type_52                  Remove  
transit_server_type_33                  Remove  
transit_server_type_4                   Remove  
In [72]:
server_data = server_data.merge(server_data_temp[['preprocess']], left_on='transit_server_type',right_index=True)
print (server_data.head())
server_data['preprocess'].value_counts()
       id     transit_server_type area_code train  outage_duration  \
0    6597  transit_server_type_11       NaN   NaN              NaN   
4    5022  transit_server_type_11       NaN   NaN              NaN   
5    6852  transit_server_type_11       NaN   NaN              NaN   
9   14838  transit_server_type_11       NaN   NaN              NaN   
11   2588  transit_server_type_11    area_1   yes              0.0   

                preprocess  
0   transit_server_type_11  
4   transit_server_type_11  
5   transit_server_type_11  
9   transit_server_type_11  
11  transit_server_type_11  
Out[72]:
transit_server_type_11    7888
transit_server_type_35    6615
transit_server_type_34    5927
transit_server_type_15    4395
transit_server_type_20    1458
transit_server_type_54     684
transit_server_type_13     582
transit_server_type_42     478
transit_server_type_44     466
transit_server_type_23     429
transit_server_type_14     330
transit_server_type_43     306
transit_server_type_22     223
transit_server_type_50     154
transit_server_type_10     145
transit_server_type_21     136
transit_server_other_0     122
transit_server_other_2      93
transit_server_type_18      73
transit_server_type_47      69
transit_server_type_26      65
transit_server_type_32      63
transit_server_type_30      60
transit_server_type_45      53
transit_server_type_24      46
transit_server_type_27      44
transit_server_type_29      42
transit_server_other_1      42
transit_server_type_40      40
transit_server_type_46      38
transit_server_type_2       37
transit_server_type_28      32
transit_server_type_8       29
Remove                       6
Name: preprocess, dtype: int64
In [73]:
server_data_merge = server_data.pivot_table(values='transit_server_type',index='id',columns='preprocess',aggfunc=lambda x: len(x), fill_value=0)
server_data_merge.shape
Out[73]:
(18552, 34)
In [74]:
server_data_merge.columns
Out[74]:
Index(['Remove', 'transit_server_other_0', 'transit_server_other_1',
       'transit_server_other_2', 'transit_server_type_10',
       'transit_server_type_11', 'transit_server_type_13',
       'transit_server_type_14', 'transit_server_type_15',
       'transit_server_type_18', 'transit_server_type_2',
       'transit_server_type_20', 'transit_server_type_21',
       'transit_server_type_22', 'transit_server_type_23',
       'transit_server_type_24', 'transit_server_type_26',
       'transit_server_type_27', 'transit_server_type_28',
       'transit_server_type_29', 'transit_server_type_30',
       'transit_server_type_32', 'transit_server_type_34',
       'transit_server_type_35', 'transit_server_type_40',
       'transit_server_type_42', 'transit_server_type_43',
       'transit_server_type_44', 'transit_server_type_45',
       'transit_server_type_46', 'transit_server_type_47',
       'transit_server_type_50', 'transit_server_type_54',
       'transit_server_type_8'],
      dtype='object', name='preprocess')
In [75]:
server_data_merge.head()
Out[75]:
preprocess Remove transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 transit_server_type_13 transit_server_type_14 transit_server_type_15 transit_server_type_18 ... transit_server_type_40 transit_server_type_42 transit_server_type_43 transit_server_type_44 transit_server_type_45 transit_server_type_46 transit_server_type_47 transit_server_type_50 transit_server_type_54 transit_server_type_8
id
1 0 0 0 0 0 1 1 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
3 0 0 0 0 0 1 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
4 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 1 0 0 0
5 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0

5 rows × 34 columns

In [76]:
server_data.shape
Out[76]:
(31170, 6)
In [77]:
server_data_train=data.merge(server_data_merge,left_on='id',right_index=True)
server_data_train.head()
Out[77]:
id area_code train outage_duration Remove transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 ... transit_server_type_40 transit_server_type_42 transit_server_type_43 transit_server_type_44 transit_server_type_45 transit_server_type_46 transit_server_type_47 transit_server_type_50 transit_server_type_54 transit_server_type_8
0 13366 area_415 yes 1.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
1 6783 area_474 yes 0.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
2 9519 area_931 yes 1.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
3 10202 area_700 yes 1.0 0 0 0 0 0 1 ... 0 0 0 0 0 0 0 0 0 0
4 4555 area_600 yes 2.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0

5 rows × 38 columns

In [78]:
server_data_train.shape
Out[78]:
(7381, 38)
In [79]:
server_temp=server_data.copy()
In [80]:
server_temp=server_temp.drop(['preprocess'],axis=1)
In [81]:
server_temp.shape
Out[81]:
(31170, 5)
In [82]:
server_temp_merge=server_temp.merge(server_data_merge,left_on='id',right_index=True)
server_temp_merge.shape
Out[82]:
(31170, 39)
In [83]:
server_temp_merge.duplicated('id').sum()
Out[83]:
12618
In [84]:
server_temp_merge.loc[server_temp_merge.duplicated(subset='id',keep=False),:]
Out[84]:
id transit_server_type area_code train outage_duration Remove transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 ... transit_server_type_40 transit_server_type_42 transit_server_type_43 transit_server_type_44 transit_server_type_45 transit_server_type_46 transit_server_type_47 transit_server_type_50 transit_server_type_54 transit_server_type_8
4 5022 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
3 5022 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
5 6852 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
6 6852 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
9 14838 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
8 14838 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
11 2588 transit_server_type_11 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
10 2588 transit_server_type_15 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
12 4848 transit_server_type_11 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
13 4848 transit_server_type_15 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
14 6914 transit_server_type_11 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
15 6914 transit_server_type_15 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
17 5337 transit_server_type_11 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
16 5337 transit_server_type_15 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
19 10460 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18 10460 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
20 15494 transit_server_type_11 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
21 15494 transit_server_type_15 area_1 yes 0.0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
22 10289 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
23 10289 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
24 8587 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
25 8587 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
26 12943 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
27 12943 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
31 3399 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
30 3399 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
33 1505 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
32 1505 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
34 9446 transit_server_type_11 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
35 9446 transit_server_type_15 NaN NaN NaN 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
28404 7253 transit_server_type_44 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28406 7253 transit_server_type_42 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28405 7253 transit_server_type_45 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28403 7253 transit_server_type_43 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28407 12876 transit_server_type_44 NaN NaN NaN 0 0 0 0 0 ... 0 1 0 1 1 0 0 0 0 0
28409 12876 transit_server_type_42 NaN NaN NaN 0 0 0 0 0 ... 0 1 0 1 1 0 0 0 0 0
28408 12876 transit_server_type_45 NaN NaN NaN 0 0 0 0 0 ... 0 1 0 1 1 0 0 0 0 0
28411 4333 transit_server_type_44 area_899 yes 0.0 0 0 0 0 0 ... 0 1 0 1 0 0 0 0 0 0
28410 4333 transit_server_type_42 area_899 yes 0.0 0 0 0 0 0 ... 0 1 0 1 0 0 0 0 0 0
28420 16359 transit_server_type_44 area_899 no NaN 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28422 16359 transit_server_type_42 area_899 no NaN 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28421 16359 transit_server_type_43 area_899 no NaN 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28428 4088 transit_server_type_44 NaN NaN NaN 0 0 0 0 0 ... 0 1 0 1 0 0 0 0 0 0
28427 4088 transit_server_type_42 NaN NaN NaN 0 0 0 0 0 ... 0 1 0 1 0 0 0 0 0 0
28430 10134 transit_server_type_44 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28431 10134 transit_server_type_42 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28429 10134 transit_server_type_43 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28432 11042 transit_server_type_44 area_899 yes 0.0 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28433 11042 transit_server_type_42 area_899 yes 0.0 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28434 11042 transit_server_type_43 area_899 yes 0.0 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28436 15376 transit_server_type_44 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28435 15376 transit_server_type_42 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28438 15376 transit_server_type_45 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28437 15376 transit_server_type_43 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28439 12025 transit_server_type_44 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28441 12025 transit_server_type_42 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28440 12025 transit_server_type_43 NaN NaN NaN 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28444 15593 transit_server_type_44 area_899 yes 1.0 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28442 15593 transit_server_type_42 area_899 yes 1.0 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28443 15593 transit_server_type_43 area_899 yes 1.0 0 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0

23155 rows × 39 columns

In [85]:
server_data_dup=server_data.merge(server_data_merge,left_on='id',right_index=True)
server_data_dup.head()
Out[85]:
id transit_server_type area_code train outage_duration preprocess Remove transit_server_other_0 transit_server_other_1 transit_server_other_2 ... transit_server_type_40 transit_server_type_42 transit_server_type_43 transit_server_type_44 transit_server_type_45 transit_server_type_46 transit_server_type_47 transit_server_type_50 transit_server_type_54 transit_server_type_8
0 6597 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
4 5022 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
3 5022 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
5 6852 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
6 6852 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0

5 rows × 40 columns

In [86]:
server_data_dup.shape
Out[86]:
(31170, 40)
In [87]:
server_data_dup.loc[server_data_dup.duplicated(subset='id',keep=False),:]
Out[87]:
id transit_server_type area_code train outage_duration preprocess Remove transit_server_other_0 transit_server_other_1 transit_server_other_2 ... transit_server_type_40 transit_server_type_42 transit_server_type_43 transit_server_type_44 transit_server_type_45 transit_server_type_46 transit_server_type_47 transit_server_type_50 transit_server_type_54 transit_server_type_8
4 5022 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
3 5022 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
5 6852 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
6 6852 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
9 14838 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
8 14838 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
11 2588 transit_server_type_11 area_1 yes 0.0 transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
10 2588 transit_server_type_15 area_1 yes 0.0 transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
12 4848 transit_server_type_11 area_1 yes 0.0 transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
13 4848 transit_server_type_15 area_1 yes 0.0 transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
14 6914 transit_server_type_11 area_1 yes 0.0 transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
15 6914 transit_server_type_15 area_1 yes 0.0 transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
17 5337 transit_server_type_11 area_1 yes 0.0 transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
16 5337 transit_server_type_15 area_1 yes 0.0 transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
19 10460 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18 10460 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
20 15494 transit_server_type_11 area_1 yes 0.0 transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
21 15494 transit_server_type_15 area_1 yes 0.0 transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
22 10289 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
23 10289 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
24 8587 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
25 8587 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
26 12943 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
27 12943 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
31 3399 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
30 3399 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
33 1505 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
32 1505 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
34 9446 transit_server_type_11 NaN NaN NaN transit_server_type_11 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
35 9446 transit_server_type_15 NaN NaN NaN transit_server_type_15 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
28404 7253 transit_server_type_44 NaN NaN NaN transit_server_type_44 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28406 7253 transit_server_type_42 NaN NaN NaN transit_server_type_42 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28405 7253 transit_server_type_45 NaN NaN NaN transit_server_type_45 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28403 7253 transit_server_type_43 NaN NaN NaN transit_server_type_43 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28407 12876 transit_server_type_44 NaN NaN NaN transit_server_type_44 0 0 0 0 ... 0 1 0 1 1 0 0 0 0 0
28409 12876 transit_server_type_42 NaN NaN NaN transit_server_type_42 0 0 0 0 ... 0 1 0 1 1 0 0 0 0 0
28408 12876 transit_server_type_45 NaN NaN NaN transit_server_type_45 0 0 0 0 ... 0 1 0 1 1 0 0 0 0 0
28411 4333 transit_server_type_44 area_899 yes 0.0 transit_server_type_44 0 0 0 0 ... 0 1 0 1 0 0 0 0 0 0
28410 4333 transit_server_type_42 area_899 yes 0.0 transit_server_type_42 0 0 0 0 ... 0 1 0 1 0 0 0 0 0 0
28420 16359 transit_server_type_44 area_899 no NaN transit_server_type_44 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28422 16359 transit_server_type_42 area_899 no NaN transit_server_type_42 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28421 16359 transit_server_type_43 area_899 no NaN transit_server_type_43 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28428 4088 transit_server_type_44 NaN NaN NaN transit_server_type_44 0 0 0 0 ... 0 1 0 1 0 0 0 0 0 0
28427 4088 transit_server_type_42 NaN NaN NaN transit_server_type_42 0 0 0 0 ... 0 1 0 1 0 0 0 0 0 0
28430 10134 transit_server_type_44 NaN NaN NaN transit_server_type_44 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28431 10134 transit_server_type_42 NaN NaN NaN transit_server_type_42 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28429 10134 transit_server_type_43 NaN NaN NaN transit_server_type_43 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28432 11042 transit_server_type_44 area_899 yes 0.0 transit_server_type_44 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28433 11042 transit_server_type_42 area_899 yes 0.0 transit_server_type_42 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28434 11042 transit_server_type_43 area_899 yes 0.0 transit_server_type_43 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28436 15376 transit_server_type_44 NaN NaN NaN transit_server_type_44 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28435 15376 transit_server_type_42 NaN NaN NaN transit_server_type_42 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28438 15376 transit_server_type_45 NaN NaN NaN transit_server_type_45 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28437 15376 transit_server_type_43 NaN NaN NaN transit_server_type_43 0 0 0 0 ... 0 1 1 1 1 0 0 0 0 0
28439 12025 transit_server_type_44 NaN NaN NaN transit_server_type_44 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28441 12025 transit_server_type_42 NaN NaN NaN transit_server_type_42 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28440 12025 transit_server_type_43 NaN NaN NaN transit_server_type_43 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28444 15593 transit_server_type_44 area_899 yes 1.0 transit_server_type_44 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28442 15593 transit_server_type_42 area_899 yes 1.0 transit_server_type_42 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0
28443 15593 transit_server_type_43 area_899 yes 1.0 transit_server_type_43 0 0 0 0 ... 0 1 1 1 0 0 0 0 0 0

23155 rows × 40 columns

Merging train and test _data with report_data

In [88]:
data.head()
Out[88]:
id area_code train outage_duration
0 13366 area_415 yes 1.0
1 6783 area_474 yes 0.0
2 9519 area_931 yes 1.0
3 10202 area_700 yes 1.0
4 4555 area_600 yes 2.0
In [89]:
report_data.head()
Out[89]:
id log_report_type volume
0 6597 log_report_type_68 6
1 8011 log_report_type_68 7
2 2597 log_report_type_68 1
3 5022 log_report_type_172 2
4 5022 log_report_type_56 1
In [90]:
report_data.shape
Out[90]:
(58671, 3)
In [91]:
data.shape
Out[91]:
(7381, 4)
In [92]:
report_data=report_data.merge(data,on='id',how='left')
report_data.head()
Out[92]:
id log_report_type volume area_code train outage_duration
0 6597 log_report_type_68 6 NaN NaN NaN
1 8011 log_report_type_68 7 area_1 yes 0.0
2 2597 log_report_type_68 1 NaN NaN NaN
3 5022 log_report_type_172 2 NaN NaN NaN
4 5022 log_report_type_56 1 NaN NaN NaN
In [93]:
report_data_temp=pd.DataFrame(report_data['log_report_type'].value_counts())
report_data_temp.head()
Out[93]:
log_report_type
log_report_type_312 5267
log_report_type_232 4754
log_report_type_82 3472
log_report_type_203 2823
log_report_type_313 2145
In [94]:
report_data_temp['Perc_Train'] = report_data.pivot_table(values='train',index='log_report_type',aggfunc=lambda x: sum(x=='yes')/float(len(x)))
report_data_temp.head()
Out[94]:
log_report_type Perc_Train
log_report_type_312 5267 0.330169
log_report_type_232 4754 0.327303
log_report_type_82 3472 0.313364
log_report_type_203 2823 0.309600
log_report_type_313 2145 0.337529
In [95]:
report_data_temp['Mode_outage_duration']=report_data.loc[report_data['train']=='yes'].pivot_table(values='outage_duration',index='log_report_type',aggfunc=lambda x: mode(x).mode[0])
In [96]:
len(report_data_temp)
Out[96]:
386
In [97]:
report_data_temp['preprocess']=report_data_temp.index.values
In [98]:
report_data_temp['preprocess'].loc[report_data_temp['Perc_Train']==1] = np.nan
In [99]:
report_data_temp[-20:]
Out[99]:
log_report_type Perc_Train Mode_outage_duration preprocess
log_report_type_297 1 0.0 NaN log_report_type_297
log_report_type_250 1 1.0 0.0 NaN
log_report_type_14 1 0.0 NaN log_report_type_14
log_report_type_180 1 1.0 0.0 NaN
log_report_type_355 1 0.0 NaN log_report_type_355
log_report_type_260 1 1.0 0.0 NaN
log_report_type_356 1 0.0 NaN log_report_type_356
log_report_type_32 1 0.0 NaN log_report_type_32
log_report_type_262 1 1.0 1.0 NaN
log_report_type_281 1 0.0 NaN log_report_type_281
log_report_type_19 1 0.0 NaN log_report_type_19
log_report_type_257 1 1.0 0.0 NaN
log_report_type_296 1 0.0 NaN log_report_type_296
log_report_type_300 1 0.0 NaN log_report_type_300
log_report_type_287 1 0.0 NaN log_report_type_287
log_report_type_325 1 1.0 0.0 NaN
log_report_type_100 1 0.0 NaN log_report_type_100
log_report_type_363 1 1.0 0.0 NaN
log_report_type_16 1 1.0 0.0 NaN
log_report_type_350 1 0.0 NaN log_report_type_350
In [100]:
report_data_temp[:5]
Out[100]:
log_report_type Perc_Train Mode_outage_duration preprocess
log_report_type_312 5267 0.330169 0.0 log_report_type_312
log_report_type_232 4754 0.327303 0.0 log_report_type_232
log_report_type_82 3472 0.313364 2.0 log_report_type_82
log_report_type_203 2823 0.309600 2.0 log_report_type_203
log_report_type_313 2145 0.337529 0.0 log_report_type_313
In [101]:
limit=140
report_data_temp['preprocess'].iloc[limit:]=report_data_temp['Mode_outage_duration'].iloc[limit:].apply(lambda x: 'Remove' if pd.isnull(x) else 'log_report_other%d'%int(x))
print (report_data_temp['preprocess'].value_counts())
report_data_temp
log_report_other0      101
Remove                  76
log_report_other1       46
log_report_other2       23
log_report_type_191      1
log_report_type_229      1
log_report_type_68       1
log_report_type_301      1
log_report_type_63       1
log_report_type_316      1
log_report_type_290      1
log_report_type_240      1
log_report_type_35       1
log_report_type_231      1
log_report_type_42       1
log_report_type_273      1
log_report_type_375      1
log_report_type_187      1
log_report_type_181      1
log_report_type_198      1
log_report_type_74       1
log_report_type_280      1
log_report_type_304      1
log_report_type_153      1
log_report_type_10       1
log_report_type_85       1
log_report_type_108      1
log_report_type_155      1
log_report_type_378      1
log_report_type_161      1
                      ... 
log_report_type_101      1
log_report_type_81       1
log_report_type_196      1
log_report_type_167      1
log_report_type_312      1
log_report_type_195      1
log_report_type_204      1
log_report_type_111      1
log_report_type_209      1
log_report_type_183      1
log_report_type_235      1
log_report_type_52       1
log_report_type_51       1
log_report_type_219      1
log_report_type_239      1
log_report_type_211      1
log_report_type_141      1
log_report_type_306      1
log_report_type_291      1
log_report_type_62       1
log_report_type_132      1
log_report_type_223      1
log_report_type_311      1
log_report_type_20       1
log_report_type_221      1
log_report_type_86       1
log_report_type_80       1
log_report_type_227      1
log_report_type_133      1
log_report_type_345      1
Name: preprocess, Length: 144, dtype: int64
Out[101]:
log_report_type Perc_Train Mode_outage_duration preprocess
log_report_type_312 5267 0.330169 0.0 log_report_type_312
log_report_type_232 4754 0.327303 0.0 log_report_type_232
log_report_type_82 3472 0.313364 2.0 log_report_type_82
log_report_type_203 2823 0.309600 2.0 log_report_type_203
log_report_type_313 2145 0.337529 0.0 log_report_type_313
log_report_type_233 1901 0.336665 0.0 log_report_type_233
log_report_type_307 1597 0.333125 0.0 log_report_type_307
log_report_type_54 1573 0.315957 1.0 log_report_type_54
log_report_type_170 1526 0.317169 1.0 log_report_type_170
log_report_type_71 1514 0.321664 0.0 log_report_type_71
log_report_type_315 1495 0.330435 0.0 log_report_type_315
log_report_type_134 1419 0.300211 0.0 log_report_type_134
log_report_type_80 1336 0.306886 0.0 log_report_type_80
log_report_type_235 1294 0.324575 0.0 log_report_type_235
log_report_type_193 1160 0.312931 0.0 log_report_type_193
log_report_type_219 1152 0.300347 0.0 log_report_type_219
log_report_type_68 1093 0.350412 0.0 log_report_type_68
log_report_type_227 1080 0.305556 0.0 log_report_type_227
log_report_type_314 950 0.345263 0.0 log_report_type_314
log_report_type_201 902 0.303769 0.0 log_report_type_201
log_report_type_234 882 0.349206 0.0 log_report_type_234
log_report_type_73 868 0.312212 0.0 log_report_type_73
log_report_type_195 783 0.301405 0.0 log_report_type_195
log_report_type_301 707 0.332390 0.0 log_report_type_301
log_report_type_309 627 0.354067 0.0 log_report_type_309
log_report_type_55 564 0.312057 0.0 log_report_type_55
log_report_type_229 560 0.358929 0.0 log_report_type_229
log_report_type_273 491 0.352342 1.0 log_report_type_273
log_report_type_308 484 0.332645 0.0 log_report_type_308
log_report_type_368 462 0.307359 0.0 log_report_type_368
... ... ... ... ...
log_report_type_272 1 0.000000 NaN Remove
log_report_type_271 1 0.000000 NaN Remove
log_report_type_336 1 1.000000 0.0 log_report_other0
log_report_type_324 1 0.000000 NaN Remove
log_report_type_13 1 0.000000 NaN Remove
log_report_type_379 1 0.000000 NaN Remove
log_report_type_12 1 0.000000 NaN Remove
log_report_type_334 1 1.000000 1.0 log_report_other1
log_report_type_286 1 0.000000 NaN Remove
log_report_type_213 1 1.000000 0.0 log_report_other0
log_report_type_297 1 0.000000 NaN Remove
log_report_type_250 1 1.000000 0.0 log_report_other0
log_report_type_14 1 0.000000 NaN Remove
log_report_type_180 1 1.000000 0.0 log_report_other0
log_report_type_355 1 0.000000 NaN Remove
log_report_type_260 1 1.000000 0.0 log_report_other0
log_report_type_356 1 0.000000 NaN Remove
log_report_type_32 1 0.000000 NaN Remove
log_report_type_262 1 1.000000 1.0 log_report_other1
log_report_type_281 1 0.000000 NaN Remove
log_report_type_19 1 0.000000 NaN Remove
log_report_type_257 1 1.000000 0.0 log_report_other0
log_report_type_296 1 0.000000 NaN Remove
log_report_type_300 1 0.000000 NaN Remove
log_report_type_287 1 0.000000 NaN Remove
log_report_type_325 1 1.000000 0.0 log_report_other0
log_report_type_100 1 0.000000 NaN Remove
log_report_type_363 1 1.000000 0.0 log_report_other0
log_report_type_16 1 1.000000 0.0 log_report_other0
log_report_type_350 1 0.000000 NaN Remove

386 rows × 4 columns

In [102]:
report_data=report_data.merge(report_data_temp[['preprocess']],left_on='log_report_type',right_index=True)
print(report_data.head())
report_data['preprocess'].value_counts()
       id     log_report_type  volume area_code train  outage_duration  \
0    6597  log_report_type_68       6       NaN   NaN              NaN   
1    8011  log_report_type_68       7    area_1   yes              0.0   
2    2597  log_report_type_68       1       NaN   NaN              NaN   
23   6914  log_report_type_68      11    area_1   yes              0.0   
41  16416  log_report_type_68       4       NaN   NaN              NaN   

            preprocess  
0   log_report_type_68  
1   log_report_type_68  
2   log_report_type_68  
23  log_report_type_68  
41  log_report_type_68  
Out[102]:
log_report_type_312    5267
log_report_type_232    4754
log_report_type_82     3472
log_report_type_203    2823
log_report_type_313    2145
log_report_type_233    1901
log_report_type_307    1597
log_report_type_54     1573
log_report_type_170    1526
log_report_type_71     1514
log_report_type_315    1495
log_report_type_134    1419
log_report_type_80     1336
log_report_type_235    1294
log_report_type_193    1160
log_report_type_219    1152
log_report_type_68     1093
log_report_type_227    1080
log_report_type_314     950
log_report_type_201     902
log_report_type_234     882
log_report_type_73      868
log_report_type_195     783
log_report_type_301     707
log_report_other0       655
log_report_type_309     627
log_report_type_55      564
log_report_type_229     560
log_report_type_273     491
log_report_type_308     484
                       ... 
log_report_type_42       36
log_report_type_161      36
log_report_type_293      34
log_report_type_101      33
log_report_type_52       31
log_report_type_157      31
log_report_type_212      30
log_report_type_353      29
log_report_type_205      29
log_report_type_231      29
log_report_type_354      29
log_report_type_35       29
log_report_type_217      28
log_report_type_118      28
log_report_type_304      28
log_report_type_105      26
log_report_type_8        26
log_report_type_20       25
log_report_type_10       24
log_report_type_127      24
log_report_type_141      24
log_report_type_187      24
log_report_type_111      24
log_report_type_108      23
log_report_type_65       23
log_report_type_277      23
log_report_type_183      23
log_report_type_150      23
log_report_type_303      23
log_report_type_168      22
Name: preprocess, Length: 144, dtype: int64
In [103]:
report_data_merge = report_data.pivot_table(values='volume',index='id',columns='preprocess',aggfunc=np.sum, fill_value=0)
report_data_merge.shape
Out[103]:
(18552, 144)
In [104]:
report_data_merge.sum().sum()
Out[104]:
568246
In [105]:
report_data_merge.head(-20)
Out[105]:
preprocess Remove log_report_other0 log_report_other1 log_report_other2 log_report_type_10 log_report_type_101 log_report_type_103 log_report_type_105 log_report_type_108 log_report_type_109 ... log_report_type_8 log_report_type_80 log_report_type_81 log_report_type_82 log_report_type_83 log_report_type_85 log_report_type_86 log_report_type_87 log_report_type_94 log_report_type_95
id
1 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
3 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
4 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
5 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
6 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
7 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
8 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
9 0 0 0 0 0 0 0 33 0 0 ... 0 0 0 15 0 0 0 0 0 0
10 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
11 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
12 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
13 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
14 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
15 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
16 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
17 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
19 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
20 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
21 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
22 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
23 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
24 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
25 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
26 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
27 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
28 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
29 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
30 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
18503 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18504 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18505 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18506 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 10 0 0 0 0 0 0
18507 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 1 0
18508 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18509 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 11 0 0 0 0 0 0
18510 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 2 0 0 0 0 0 0
18511 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18512 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18513 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18514 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18515 0 2 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 1 1
18516 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18517 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18518 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18519 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 2 0 0
18520 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18521 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18522 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18523 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18524 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 11 0 0 0 0 0 0
18525 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 44 0 0 0 0 0 0
18526 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18527 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18528 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 14 0 0 0 0 0 0
18529 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18530 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18531 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
18532 0 0 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0

18532 rows × 144 columns

In [106]:
server_report_train=server_data_train.merge(report_data_merge,left_on='id',right_index=True)
print(server_report_train.shape)
server_report_train.head()
(7381, 182)
Out[106]:
id area_code train outage_duration Remove_x transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 ... log_report_type_8 log_report_type_80 log_report_type_81 log_report_type_82 log_report_type_83 log_report_type_85 log_report_type_86 log_report_type_87 log_report_type_94 log_report_type_95
0 13366 area_415 yes 1.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
1 6783 area_474 yes 0.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
2 9519 area_931 yes 1.0 0 0 0 0 0 0 ... 0 2 0 6 0 0 0 0 0 0
3 10202 area_700 yes 1.0 0 0 0 0 0 1 ... 0 0 0 0 0 0 0 0 0 0
4 4555 area_600 yes 2.0 0 0 0 0 0 0 ... 0 0 0 30 0 0 0 0 0 0

5 rows × 182 columns

Merging train and test _data with outage_data

In [107]:
outage_data.head()
Out[107]:
id outage_type
0 6597 outage_type_2
1 8011 outage_type_2
2 2597 outage_type_2
3 5022 outage_type_1
4 6852 outage_type_1
In [108]:
data.head()
Out[108]:
id area_code train outage_duration
0 13366 area_415 yes 1.0
1 6783 area_474 yes 0.0
2 9519 area_931 yes 1.0
3 10202 area_700 yes 1.0
4 4555 area_600 yes 2.0
In [109]:
outage_data['outage_type'].value_counts()
Out[109]:
outage_type_2    8737
outage_type_1    8728
outage_type_4    1014
outage_type_5      65
outage_type_3       8
Name: outage_type, dtype: int64
In [110]:
outage_data=outage_data.merge(data,on='id',how='left')
outage_data.head()
Out[110]:
id outage_type area_code train outage_duration
0 6597 outage_type_2 NaN NaN NaN
1 8011 outage_type_2 area_1 yes 0.0
2 2597 outage_type_2 NaN NaN NaN
3 5022 outage_type_1 NaN NaN NaN
4 6852 outage_type_1 NaN NaN NaN
In [111]:
outage_data.shape
Out[111]:
(18552, 5)
In [112]:
outage_data_temp = pd.DataFrame(outage_data['outage_type'].value_counts())
outage_data_temp.head()
Out[112]:
outage_type
outage_type_2 8737
outage_type_1 8728
outage_type_4 1014
outage_type_5 65
outage_type_3 8
In [113]:
outage_data_temp['Perc_Train']=outage_data.pivot_table(values='train',index='outage_type',aggfunc=lambda x: sum(x=='yes')/float(len(x)))
outage_data_temp.head()
Out[113]:
outage_type Perc_Train
outage_type_2 8737 0.329060
outage_type_1 8728 0.309349
outage_type_4 1014 0.301775
outage_type_5 65 0.292308
outage_type_3 8 0.500000
In [114]:
outage_data_temp['Mode_outage_duration']=outage_data.loc[outage_data['train']=='yes'].pivot_table(values='outage_duration',index='outage_type',aggfunc=lambda x: mode(x).mode[0])
outage_data_temp
Out[114]:
outage_type Perc_Train Mode_outage_duration
outage_type_2 8737 0.329060 0.0
outage_type_1 8728 0.309349 0.0
outage_type_4 1014 0.301775 0.0
outage_type_5 65 0.292308 1.0
outage_type_3 8 0.500000 0.0
In [115]:
outage_data.loc[server_data['train']=='yes'].pivot_table(values='outage_duration',index='outage_type', aggfunc=lambda x: mode(x))
Out[115]:
outage_duration
outage_type
outage_type_1 ([0.0], [451.0])
outage_type_2 ([0.0], [681.0])
outage_type_3 ([nan], [1.0])
outage_type_4 ([0.0], [100.0])
outage_type_5 ([nan], [1.0])
In [116]:
outage_data_merge = outage_data.pivot_table(values='train',index='id',columns='outage_type',aggfunc=lambda x: len(x), fill_value=0)
outage_data_merge.head()
Out[116]:
outage_type outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5
id
1 1 0 0 0 0
2 0 1 0 0 0
3 1 0 0 0 0
4 0 0 0 1 0
5 0 1 0 0 0

Merging train and test_data with broadband_data

In [117]:
broadband_data.head()
Out[117]:
id broadband_type
0 6597 broadband_type_8
1 8011 broadband_type_8
2 2597 broadband_type_8
3 5022 broadband_type_8
4 6852 broadband_type_8
In [118]:
broadband_data.shape
Out[118]:
(21076, 2)
In [119]:
broadband_data['broadband_type'].value_counts()
Out[119]:
broadband_type_8     10268
broadband_type_2      8918
broadband_type_6       582
broadband_type_7       498
broadband_type_4       330
broadband_type_9       190
broadband_type_3       145
broadband_type_10       73
broadband_type_1        58
broadband_type_5        14
Name: broadband_type, dtype: int64
In [120]:
broadband_data = broadband_data.merge(data, on='id',how='left')
broadband_data.head()
Out[120]:
id broadband_type area_code train outage_duration
0 6597 broadband_type_8 NaN NaN NaN
1 8011 broadband_type_8 area_1 yes 0.0
2 2597 broadband_type_8 NaN NaN NaN
3 5022 broadband_type_8 NaN NaN NaN
4 6852 broadband_type_8 NaN NaN NaN
In [121]:
broadband_data.shape
Out[121]:
(21076, 5)
In [122]:
broadband_data_temp = pd.DataFrame(broadband_data['broadband_type'].value_counts())
broadband_data_temp.head()
Out[122]:
broadband_type
broadband_type_8 10268
broadband_type_2 8918
broadband_type_6 582
broadband_type_7 498
broadband_type_4 330
In [123]:
broadband_data_temp['PercT_rain'] = broadband_data.pivot_table(values='train',index='broadband_type',aggfunc=lambda x: sum(x=='yes')/float(len(x)))
broadband_data_temp
Out[123]:
broadband_type PercT_rain
broadband_type_8 10268 0.316128
broadband_type_2 8918 0.319691
broadband_type_6 582 0.345361
broadband_type_7 498 0.359438
broadband_type_4 330 0.345455
broadband_type_9 190 0.321053
broadband_type_3 145 0.303448
broadband_type_10 73 0.383562
broadband_type_1 58 0.465517
broadband_type_5 14 0.214286
In [124]:
broadband_data_temp['Mode_outage_duration'] = broadband_data.loc[broadband_data['train']=='yes'].pivot_table(values='outage_duration',index='broadband_type', aggfunc=lambda x: mode(x).mode[0])
broadband_data_temp
Out[124]:
broadband_type PercT_rain Mode_outage_duration
broadband_type_8 10268 0.316128 0.0
broadband_type_2 8918 0.319691 0.0
broadband_type_6 582 0.345361 1.0
broadband_type_7 498 0.359438 0.0
broadband_type_4 330 0.345455 0.0
broadband_type_9 190 0.321053 0.0
broadband_type_3 145 0.303448 1.0
broadband_type_10 73 0.383562 0.0
broadband_type_1 58 0.465517 1.0
broadband_type_5 14 0.214286 2.0
In [125]:
broadband_data.loc[broadband_data['broadband_type']=='broadband_type_5']
Out[125]:
id broadband_type area_code train outage_duration
5653 5475 broadband_type_5 NaN NaN NaN
5655 5915 broadband_type_5 NaN NaN NaN
5657 9989 broadband_type_5 NaN NaN NaN
6936 7378 broadband_type_5 area_32 yes 2.0
7064 9373 broadband_type_5 area_33 yes 2.0
7073 9677 broadband_type_5 NaN NaN NaN
7223 6322 broadband_type_5 NaN NaN NaN
10786 15655 broadband_type_5 NaN NaN NaN
10790 11683 broadband_type_5 NaN NaN NaN
10793 1616 broadband_type_5 area_541 yes 2.0
14805 78 broadband_type_5 NaN NaN NaN
14813 8140 broadband_type_5 NaN NaN NaN
17159 3031 broadband_type_5 NaN NaN NaN
17988 6981 broadband_type_5 area_86 no NaN
In [126]:
broadband_data_merge = broadband_data.pivot_table(values='train',index='id',columns='broadband_type',aggfunc=lambda x: len(x), fill_value=0)
broadband_data_merge.head()
Out[126]:
broadband_type broadband_type_1 broadband_type_10 broadband_type_2 broadband_type_3 broadband_type_4 broadband_type_5 broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9
id
1 0 0 0 0 0 0 1 0 1 0
2 0 0 1 0 0 0 0 0 0 0
3 0 0 0 0 0 0 0 0 1 0
4 0 0 1 0 0 0 0 0 0 0
5 0 0 1 0 0 0 0 0 0 0
In [127]:
server_report_broadband_train=server_report_train.merge(broadband_data_merge,left_on='id',right_index=True)
print(server_report_broadband_train.shape)
server_report_broadband_train.head()
(7381, 192)
Out[127]:
id area_code train outage_duration Remove_x transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 ... broadband_type_1 broadband_type_10 broadband_type_2 broadband_type_3 broadband_type_4 broadband_type_5 broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9
0 13366 area_415 yes 1.0 0 0 0 0 0 0 ... 0 0 1 0 0 0 0 0 0 0
1 6783 area_474 yes 0.0 0 0 0 0 0 0 ... 0 0 1 0 0 0 0 0 0 0
2 9519 area_931 yes 1.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 1 0
3 10202 area_700 yes 1.0 0 0 0 0 0 1 ... 0 0 0 0 0 0 0 0 1 0
4 4555 area_600 yes 2.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 1 0

5 rows × 192 columns

In [128]:
server_report_broadband_outage_train=server_report_broadband_train.merge(outage_data_merge,left_on='id',right_index=True)
server_report_broadband_outage_train.head()
Out[128]:
id area_code train outage_duration Remove_x transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 ... broadband_type_5 broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5
0 13366 area_415 yes 1.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 1 0
1 6783 area_474 yes 0.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 1 0 0 0
2 9519 area_931 yes 1.0 0 0 0 0 0 0 ... 0 0 0 1 0 0 1 0 0 0
3 10202 area_700 yes 1.0 0 0 0 0 0 1 ... 0 0 0 1 0 1 0 0 0 0
4 4555 area_600 yes 2.0 0 0 0 0 0 0 ... 0 0 0 1 0 0 1 0 0 0

5 rows × 197 columns

In [129]:
server_report_broadband_outage_train.dtypes
Out[129]:
id                          int64
area_code                  object
train                      object
outage_duration           float64
Remove_x                    int64
transit_server_other_0      int64
transit_server_other_1      int64
transit_server_other_2      int64
transit_server_type_10      int64
transit_server_type_11      int64
transit_server_type_13      int64
transit_server_type_14      int64
transit_server_type_15      int64
transit_server_type_18      int64
transit_server_type_2       int64
transit_server_type_20      int64
transit_server_type_21      int64
transit_server_type_22      int64
transit_server_type_23      int64
transit_server_type_24      int64
transit_server_type_26      int64
transit_server_type_27      int64
transit_server_type_28      int64
transit_server_type_29      int64
transit_server_type_30      int64
transit_server_type_32      int64
transit_server_type_34      int64
transit_server_type_35      int64
transit_server_type_40      int64
transit_server_type_42      int64
                           ...   
log_report_type_71          int64
log_report_type_73          int64
log_report_type_74          int64
log_report_type_75          int64
log_report_type_76          int64
log_report_type_8           int64
log_report_type_80          int64
log_report_type_81          int64
log_report_type_82          int64
log_report_type_83          int64
log_report_type_85          int64
log_report_type_86          int64
log_report_type_87          int64
log_report_type_94          int64
log_report_type_95          int64
broadband_type_1            int64
broadband_type_10           int64
broadband_type_2            int64
broadband_type_3            int64
broadband_type_4            int64
broadband_type_5            int64
broadband_type_6            int64
broadband_type_7            int64
broadband_type_8            int64
broadband_type_9            int64
outage_type_1               int64
outage_type_2               int64
outage_type_3               int64
outage_type_4               int64
outage_type_5               int64
Length: 197, dtype: object
In [130]:
server_report_broadband_outage_train.shape
Out[130]:
(7381, 197)
In [131]:
broadband_data['broadband_type'].value_counts()
Out[131]:
broadband_type_8     10268
broadband_type_2      8918
broadband_type_6       582
broadband_type_7       498
broadband_type_4       330
broadband_type_9       190
broadband_type_3       145
broadband_type_10       73
broadband_type_1        58
broadband_type_5        14
Name: broadband_type, dtype: int64
In [132]:
report_data['log_report_type'].value_counts()
Out[132]:
log_report_type_312    5267
log_report_type_232    4754
log_report_type_82     3472
log_report_type_203    2823
log_report_type_313    2145
log_report_type_233    1901
log_report_type_307    1597
log_report_type_54     1573
log_report_type_170    1526
log_report_type_71     1514
log_report_type_315    1495
log_report_type_134    1419
log_report_type_80     1336
log_report_type_235    1294
log_report_type_193    1160
log_report_type_219    1152
log_report_type_68     1093
log_report_type_227    1080
log_report_type_314     950
log_report_type_201     902
log_report_type_234     882
log_report_type_73      868
log_report_type_195     783
log_report_type_301     707
log_report_type_309     627
log_report_type_55      564
log_report_type_229     560
log_report_type_273     491
log_report_type_308     484
log_report_type_368     462
                       ... 
log_report_type_272       1
log_report_type_271       1
log_report_type_336       1
log_report_type_324       1
log_report_type_13        1
log_report_type_379       1
log_report_type_12        1
log_report_type_334       1
log_report_type_286       1
log_report_type_213       1
log_report_type_297       1
log_report_type_250       1
log_report_type_14        1
log_report_type_180       1
log_report_type_355       1
log_report_type_260       1
log_report_type_356       1
log_report_type_32        1
log_report_type_262       1
log_report_type_281       1
log_report_type_19        1
log_report_type_257       1
log_report_type_296       1
log_report_type_300       1
log_report_type_287       1
log_report_type_325       1
log_report_type_100       1
log_report_type_363       1
log_report_type_16        1
log_report_type_350       1
Name: log_report_type, Length: 386, dtype: int64
In [133]:
report_data.head()
Out[133]:
id log_report_type volume area_code train outage_duration preprocess
0 6597 log_report_type_68 6 NaN NaN NaN log_report_type_68
1 8011 log_report_type_68 7 area_1 yes 0.0 log_report_type_68
2 2597 log_report_type_68 1 NaN NaN NaN log_report_type_68
23 6914 log_report_type_68 11 area_1 yes 0.0 log_report_type_68
41 16416 log_report_type_68 4 NaN NaN NaN log_report_type_68
In [134]:
train_outage_2=train_data.loc[train_data.outage_duration==2]
In [135]:
server_report_broadband_outage_train.shape
Out[135]:
(7381, 197)
In [136]:
broadband_outage2=broadband_data.loc[broadband_data.outage_duration==2]
In [137]:
broadband_outage2.shape
Out[137]:
(700, 5)
In [138]:
broadband_outage2.head()
Out[138]:
id broadband_type area_code train outage_duration
29 4703 broadband_type_8 area_1 yes 2.0
36 17595 broadband_type_8 area_1 yes 2.0
233 11037 broadband_type_8 area_1008 yes 2.0
235 15801 broadband_type_8 area_1008 yes 2.0
239 10511 broadband_type_8 area_1008 yes 2.0

Visualization

The below plot shows the area code with long outage durations

In [139]:
import plotly
import plotly.offline as pyoff
# import plotly.figure_factory as ff
from plotly.offline import init_notebook_mode, iplot, plot
import plotly.graph_objs as go

%matplotlib notebook
In [140]:
temp = train_outage_2.area_code.value_counts()
In [141]:
data = [go.Bar(
            x=temp.index,
            y=temp)]
iplot(data)
In [142]:
broadband_temp= broadband_outage2.broadband_type.value_counts()
In [143]:
broadband_temp
Out[143]:
broadband_type_8     544
broadband_type_2      82
broadband_type_6      26
broadband_type_4      17
broadband_type_1      10
broadband_type_7       9
broadband_type_3       4
broadband_type_9       4
broadband_type_5       3
broadband_type_10      1
Name: broadband_type, dtype: int64
In [144]:
data = [go.Bar(
            x=broadband_temp.index,
            y=np.round(broadband_temp.astype(float)/broadband_temp.values.sum(),2),
            text = np.round(broadband_temp.astype(float)/broadband_temp.values.sum(),2),
            textposition = 'auto')]
    

iplot(data)

Above plot shows that broadband type 8 i.e. ADSL-1 is more susceptable to long outage duration

Based on domain knowledge we know that the types of broadband connects can be classified into Digital subscriber line(DSL), Cable modem, Fibre and Broadband over powerlines (BPL).

We can now classify ADSL 1, ADSL 2, ADSL 2+ as DSL

Fibre 1, Fibre 2, Fibre Highspeed, Fibre ultra, Fibre ultra max as Fibre

We also have cable and BPL

In [145]:
broadband_data['last_digit']=broadband_data['broadband_type'].apply(lambda x: int(x.split("_")[2]))
In [146]:
broadband_data.head()
Out[146]:
id broadband_type area_code train outage_duration last_digit
0 6597 broadband_type_8 NaN NaN NaN 8
1 8011 broadband_type_8 area_1 yes 0.0 8
2 2597 broadband_type_8 NaN NaN NaN 8
3 5022 broadband_type_8 NaN NaN NaN 8
4 6852 broadband_type_8 NaN NaN NaN 8
In [147]:
broadband_data['classification']=broadband_data['last_digit'].apply(lambda x: 'DSL' if x==8 else ('DSL' if x==2 else('DSL' if x==6 else('Cable' if x==7 else('BPL' if x==9 else 'Fibre')))))
In [148]:
broadband_data.head()
Out[148]:
id broadband_type area_code train outage_duration last_digit classification
0 6597 broadband_type_8 NaN NaN NaN 8 DSL
1 8011 broadband_type_8 area_1 yes 0.0 8 DSL
2 2597 broadband_type_8 NaN NaN NaN 8 DSL
3 5022 broadband_type_8 NaN NaN NaN 8 DSL
4 6852 broadband_type_8 NaN NaN NaN 8 DSL
In [149]:
broadband_classified=broadband_data.loc[broadband_data.outage_duration==2].classification.value_counts()
In [150]:
data = [go.Bar(
            x=broadband_classified.index,
            y=np.round(broadband_classified.astype(float)/broadband_classified.values.sum(),2),
            text = np.round(broadband_classified.astype(float)/broadband_classified.values.sum(),2),
            textposition = 'auto')]
    

iplot(data)

We can see that DSL type of broadband connection is having more outage duration(93%) than others in above plot.

Below plot shows the broadband connection preference.

In [151]:
broadband_alloutage=broadband_data.broadband_type.value_counts()
In [152]:
data = [go.Bar(
            x=broadband_alloutage.index,
            y=np.round(broadband_alloutage.astype(float)/broadband_alloutage.values.sum(),2),
            text = np.round(broadband_alloutage.astype(float)/broadband_alloutage.values.sum(),2),
            textposition = 'auto')]
    

iplot(data)
In [153]:
broadband_alloutage_classified=broadband_data.classification.value_counts()
In [154]:
data = [go.Bar(
            x=broadband_alloutage_classified.index,
            y=np.round(broadband_alloutage_classified.astype(float)/broadband_alloutage_classified.values.sum(),2),
            text = np.round(broadband_alloutage_classified.astype(float)/broadband_alloutage_classified.values.sum(),2),
            textposition = 'auto')]
    

iplot(data)
In [155]:
outage_data.head()
Out[155]:
id outage_type area_code train outage_duration
0 6597 outage_type_2 NaN NaN NaN
1 8011 outage_type_2 area_1 yes 0.0
2 2597 outage_type_2 NaN NaN NaN
3 5022 outage_type_1 NaN NaN NaN
4 6852 outage_type_1 NaN NaN NaN

Count plot for Outage type

In [156]:
outage_data_plot=outage_data.dropna()
In [157]:
outage_data_plot.head()
Out[157]:
id outage_type area_code train outage_duration
1 8011 outage_type_2 area_1 yes 0.0
7 2588 outage_type_1 area_1 yes 0.0
8 4848 outage_type_1 area_1 yes 0.0
9 6914 outage_type_1 area_1 yes 0.0
10 5337 outage_type_1 area_1 yes 0.0

Below plot shows how each outage type has contributed to outage duration, also we can infer that outage_type 1 and 2 are contributing more to long outage duration.

In [158]:
plt.figure(figsize=(10,5))
sns.countplot(x="outage_type", hue="outage_duration", data=outage_data_plot)
Out[158]:
<matplotlib.axes._subplots.AxesSubplot at 0x1e4dc4ade48>
In [159]:
report_data.head()
Out[159]:
id log_report_type volume area_code train outage_duration preprocess
0 6597 log_report_type_68 6 NaN NaN NaN log_report_type_68
1 8011 log_report_type_68 7 area_1 yes 0.0 log_report_type_68
2 2597 log_report_type_68 1 NaN NaN NaN log_report_type_68
23 6914 log_report_type_68 11 area_1 yes 0.0 log_report_type_68
41 16416 log_report_type_68 4 NaN NaN NaN log_report_type_68
In [160]:
report_volume=report_data.groupby('log_report_type').volume.sum()
In [161]:
report_largest=report_data.groupby('log_report_type').volume.sum().nlargest(30)

Below is the plot for top 30 log_report_type based on volume

In [162]:
data = [go.Bar(
            x=report_largest.index,
            y=report_largest,
            text = report_largest,
            textposition = 'auto')]
    

iplot(data)
In [163]:
server_data.transit_server_type.value_counts()
Out[163]:
transit_server_type_11    7888
transit_server_type_35    6615
transit_server_type_34    5927
transit_server_type_15    4395
transit_server_type_20    1458
transit_server_type_54     684
transit_server_type_13     582
transit_server_type_42     478
transit_server_type_44     466
transit_server_type_23     429
transit_server_type_14     330
transit_server_type_43     306
transit_server_type_22     223
transit_server_type_50     154
transit_server_type_10     145
transit_server_type_21     136
transit_server_type_18      73
transit_server_type_47      69
transit_server_type_26      65
transit_server_type_32      63
transit_server_type_30      60
transit_server_type_45      53
transit_server_type_24      46
transit_server_type_27      44
transit_server_type_29      42
transit_server_type_40      40
transit_server_type_46      38
transit_server_type_2       37
transit_server_type_28      32
transit_server_type_8       29
transit_server_type_6       28
transit_server_type_5       26
transit_server_type_7       24
transit_server_type_38      19
transit_server_type_3       19
transit_server_type_36      18
transit_server_type_39      18
transit_server_type_53      17
transit_server_type_49      17
transit_server_type_9       14
transit_server_type_19      14
transit_server_type_37      10
transit_server_type_31      10
transit_server_type_12       6
transit_server_type_25       5
transit_server_type_1        4
transit_server_type_51       4
transit_server_type_17       3
transit_server_type_41       2
transit_server_type_48       2
transit_server_type_52       1
transit_server_type_33       1
transit_server_type_4        1
Name: transit_server_type, dtype: int64
In [164]:
server_plot=server_data.preprocess.value_counts()

Below plot shows transit_server_type count plot with only 34 features. Rest are combined into 3 new features.

In [165]:
data = [go.Bar(
            x=server_plot.index,
            y=server_plot,
            text = server_plot,
            textposition = 'auto')]
    

iplot(data)
In [166]:
server_report_broadband_outage_train.shape
Out[166]:
(7381, 197)
In [167]:
server_report_broadband_outage_train.head()
Out[167]:
id area_code train outage_duration Remove_x transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 ... broadband_type_5 broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5
0 13366 area_415 yes 1.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 1 0
1 6783 area_474 yes 0.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 1 0 0 0
2 9519 area_931 yes 1.0 0 0 0 0 0 0 ... 0 0 0 1 0 0 1 0 0 0
3 10202 area_700 yes 1.0 0 0 0 0 0 1 ... 0 0 0 1 0 1 0 0 0 0
4 4555 area_600 yes 2.0 0 0 0 0 0 0 ... 0 0 0 1 0 0 1 0 0 0

5 rows × 197 columns

In [168]:
[x for x in server_report_broadband_outage_train.columns if 'Remove' in x]
Out[168]:
['Remove_x', 'Remove_y']
In [169]:
server_report_broadband_outage_train.drop(['Remove_x','Remove_y'],axis=1,inplace=True)
In [170]:
server_report_broadband_outage_train.head()
Out[170]:
id area_code train outage_duration transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 transit_server_type_13 ... broadband_type_5 broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5
0 13366 area_415 yes 1.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 1 0
1 6783 area_474 yes 0.0 0 0 0 0 0 0 ... 0 0 0 0 0 0 1 0 0 0
2 9519 area_931 yes 1.0 0 0 0 0 0 0 ... 0 0 0 1 0 0 1 0 0 0
3 10202 area_700 yes 1.0 0 0 0 0 1 0 ... 0 0 0 1 0 1 0 0 0 0
4 4555 area_600 yes 2.0 0 0 0 0 0 0 ... 0 0 0 1 0 0 1 0 0 0

5 rows × 195 columns

In [171]:
server_report_broadband_outage_train.shape
Out[171]:
(7381, 195)
In [172]:
data_new=server_report_broadband_outage_train.copy()
In [173]:
x=data_new.pop('outage_duration')
In [174]:
data_new['outage_duration']=x
data_new.head()
Out[174]:
id area_code train transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 transit_server_type_13 transit_server_type_14 ... broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5 outage_duration
0 13366 area_415 yes 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 1 0 1.0
1 6783 area_474 yes 0 0 0 0 0 0 0 ... 0 0 0 0 0 1 0 0 0 0.0
2 9519 area_931 yes 0 0 0 0 0 0 0 ... 0 0 1 0 0 1 0 0 0 1.0
3 10202 area_700 yes 0 0 0 0 1 0 0 ... 0 0 1 0 1 0 0 0 0 1.0
4 4555 area_600 yes 0 0 0 0 0 0 0 ... 0 0 1 0 0 1 0 0 0 2.0

5 rows × 195 columns

train_new is the new train data after EDA

We can get better insights on outage duration based on log_report_type and transit_server_type. We have to see the outage duration for the logreport with large volumns and also check trainsit_server counts and relate it to outage duration. This can help us with detecting outage duration better.

In [175]:
data_new.shape
Out[175]:
(7381, 195)
In [176]:
data_new.dtypes
Out[176]:
id                          int64
area_code                  object
train                      object
transit_server_other_0      int64
transit_server_other_1      int64
transit_server_other_2      int64
transit_server_type_10      int64
transit_server_type_11      int64
transit_server_type_13      int64
transit_server_type_14      int64
transit_server_type_15      int64
transit_server_type_18      int64
transit_server_type_2       int64
transit_server_type_20      int64
transit_server_type_21      int64
transit_server_type_22      int64
transit_server_type_23      int64
transit_server_type_24      int64
transit_server_type_26      int64
transit_server_type_27      int64
transit_server_type_28      int64
transit_server_type_29      int64
transit_server_type_30      int64
transit_server_type_32      int64
transit_server_type_34      int64
transit_server_type_35      int64
transit_server_type_40      int64
transit_server_type_42      int64
transit_server_type_43      int64
transit_server_type_44      int64
                           ...   
log_report_type_73          int64
log_report_type_74          int64
log_report_type_75          int64
log_report_type_76          int64
log_report_type_8           int64
log_report_type_80          int64
log_report_type_81          int64
log_report_type_82          int64
log_report_type_83          int64
log_report_type_85          int64
log_report_type_86          int64
log_report_type_87          int64
log_report_type_94          int64
log_report_type_95          int64
broadband_type_1            int64
broadband_type_10           int64
broadband_type_2            int64
broadband_type_3            int64
broadband_type_4            int64
broadband_type_5            int64
broadband_type_6            int64
broadband_type_7            int64
broadband_type_8            int64
broadband_type_9            int64
outage_type_1               int64
outage_type_2               int64
outage_type_3               int64
outage_type_4               int64
outage_type_5               int64
outage_duration           float64
Length: 195, dtype: object
In [177]:
data_new.head()
Out[177]:
id area_code train transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 transit_server_type_13 transit_server_type_14 ... broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5 outage_duration
0 13366 area_415 yes 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 1 0 1.0
1 6783 area_474 yes 0 0 0 0 0 0 0 ... 0 0 0 0 0 1 0 0 0 0.0
2 9519 area_931 yes 0 0 0 0 0 0 0 ... 0 0 1 0 0 1 0 0 0 1.0
3 10202 area_700 yes 0 0 0 0 1 0 0 ... 0 0 1 0 1 0 0 0 0 1.0
4 4555 area_600 yes 0 0 0 0 0 0 0 ... 0 0 1 0 0 1 0 0 0 2.0

5 rows × 195 columns

In [178]:
le = LabelEncoder()
data_new['area_code'] = le.fit_transform(data_new['area_code'])
In [179]:
data_new.head()
Out[179]:
id area_code train transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 transit_server_type_13 transit_server_type_14 ... broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5 outage_duration
0 13366 407 yes 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 1 0 1.0
1 6783 460 yes 0 0 0 0 0 0 0 ... 0 0 0 0 0 1 0 0 0 0.0
2 9519 870 yes 0 0 0 0 0 0 0 ... 0 0 1 0 0 1 0 0 0 1.0
3 10202 657 yes 0 0 0 0 1 0 0 ... 0 0 1 0 1 0 0 0 0 1.0
4 4555 569 yes 0 0 0 0 0 0 0 ... 0 0 1 0 0 1 0 0 0 2.0

5 rows × 195 columns

In [180]:
train=data_new.loc[data_new.train=='yes']
In [181]:
train.tail(5)
Out[181]:
id area_code train transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 transit_server_type_13 transit_server_type_14 ... broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5 outage_duration
5899 1910 397 yes 0 0 0 0 0 0 0 ... 0 0 0 0 0 1 0 0 0 1.0
5900 10475 764 yes 0 0 0 0 1 0 0 ... 0 0 1 0 1 0 0 0 0 0.0
5901 10675 739 yes 0 0 0 0 0 0 0 ... 0 0 1 0 0 1 0 0 0 2.0
5902 14714 216 yes 0 0 0 0 0 0 0 ... 0 0 0 0 0 1 0 0 0 0.0
5903 6253 7 yes 0 0 0 0 0 0 0 ... 0 0 1 0 0 1 0 0 0 0.0

5 rows × 195 columns

In [182]:
train=train.drop('train',axis=1)
train.head()
Out[182]:
id area_code transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 transit_server_type_13 transit_server_type_14 transit_server_type_15 ... broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5 outage_duration
0 13366 407 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 1 0 1.0
1 6783 460 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 1 0 0 0 0.0
2 9519 870 0 0 0 0 0 0 0 1 ... 0 0 1 0 0 1 0 0 0 1.0
3 10202 657 0 0 0 0 1 0 0 0 ... 0 0 1 0 1 0 0 0 0 1.0
4 4555 569 0 0 0 0 0 0 0 1 ... 0 0 1 0 0 1 0 0 0 2.0

5 rows × 194 columns

In [183]:
test=data_new.loc[data_new.train=='no']
In [185]:
test=test.drop('train',axis=1)
test.head()
Out[185]:
id area_code transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 transit_server_type_13 transit_server_type_14 transit_server_type_15 ... broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5 outage_duration
5904 3340 342 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 1 0 NaN
5905 14067 872 0 0 0 0 0 0 0 0 ... 0 0 0 0 1 0 0 0 0 NaN
5906 1134 171 0 0 0 0 1 0 0 0 ... 0 0 1 0 1 0 0 0 0 NaN
5907 27 734 0 0 0 0 1 0 0 0 ... 0 0 1 0 1 0 0 0 0 NaN
5908 9793 342 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 1 0 0 0 NaN

5 rows × 194 columns

Train Test Split

In [186]:
X, Y = train.loc[:,train.columns!='outage_duration'], train.loc[:,'outage_duration']
X_train, X_test, Y_train, Y_test = train_test_split(X, Y, test_size=0.3, stratify =Y,random_state=123)

logistic regression

In [187]:
from sklearn.metrics import accuracy_score, classification_report
In [188]:
clf = LogisticRegression()
clf = clf.fit(X_train, Y_train)
traine_pred = clf.predict(X_train)
teste_pred = clf.predict(X_test)
In [189]:
print(classification_report(Y_test,teste_pred))
              precision    recall  f1-score   support

         0.0       0.69      0.97      0.81      1149
         1.0       0.51      0.04      0.08       449
         2.0       0.60      0.42      0.49       174

    accuracy                           0.68      1772
   macro avg       0.60      0.48      0.46      1772
weighted avg       0.64      0.68      0.59      1772

In [190]:
print(classification_report(Y_train,traine_pred))
              precision    recall  f1-score   support

         0.0       0.68      0.96      0.80      2678
         1.0       0.41      0.03      0.06      1047
         2.0       0.60      0.39      0.47       407

    accuracy                           0.67      4132
   macro avg       0.56      0.46      0.44      4132
weighted avg       0.60      0.67      0.58      4132

Random Forest

In [191]:
clf1=RandomForestClassifier()
clf1 = clf1.fit(X_train, Y_train)
traine_pred = clf1.predict(X_train)
teste_pred = clf1.predict(X_test)
In [192]:
print(classification_report(Y_train, traine_pred))
print(classification_report(Y_test, teste_pred))
              precision    recall  f1-score   support

         0.0       1.00      1.00      1.00      2678
         1.0       1.00      1.00      1.00      1047
         2.0       1.00      1.00      1.00       407

    accuracy                           1.00      4132
   macro avg       1.00      1.00      1.00      4132
weighted avg       1.00      1.00      1.00      4132

              precision    recall  f1-score   support

         0.0       0.82      0.85      0.83      1149
         1.0       0.61      0.52      0.56       449
         2.0       0.63      0.71      0.67       174

    accuracy                           0.75      1772
   macro avg       0.69      0.69      0.69      1772
weighted avg       0.75      0.75      0.75      1772

decision tree

In [193]:
clf2 = tree.DecisionTreeClassifier()
clf2 = clf2.fit(X_train, Y_train)
traine_pred_df = clf2.predict(X_train)
teste_pred_df = clf2.predict(X_test)
In [194]:
print(classification_report(Y_train,traine_pred_df))
print('\n')
print('\n')
print(classification_report(Y_test,teste_pred_df))
              precision    recall  f1-score   support

         0.0       1.00      1.00      1.00      2678
         1.0       1.00      1.00      1.00      1047
         2.0       1.00      1.00      1.00       407

    accuracy                           1.00      4132
   macro avg       1.00      1.00      1.00      4132
weighted avg       1.00      1.00      1.00      4132





              precision    recall  f1-score   support

         0.0       0.78      0.77      0.78      1149
         1.0       0.47      0.45      0.46       449
         2.0       0.51      0.58      0.54       174

    accuracy                           0.67      1772
   macro avg       0.59      0.60      0.59      1772
weighted avg       0.67      0.67      0.67      1772

Grid Search cv

In [195]:
param_grid = {"criterion": ['entropy'],
             'min_samples_split': [5,10,20],
             'max_depth': [2,5,10,15,30],
             'max_leaf_nodes': [100,120,135,150], }
In [196]:
dt = tree.DecisionTreeClassifier()
clf_3_cv = GridSearchCV(dt, param_grid, cv = 5,n_jobs=-1)
clf_3_cv.fit(X_train, Y_train)
traine_pred = clf_3_cv.predict(X_train)
teste_pred = clf_3_cv.predict(X_test)
In [197]:
clf_3_cv.best_params_
Out[197]:
{'criterion': 'entropy',
 'max_depth': 30,
 'max_leaf_nodes': 100,
 'min_samples_split': 5}
In [198]:
print(classification_report(Y_train,traine_pred))
print('\n')
print('\n')
print(classification_report(Y_test,teste_pred))
              precision    recall  f1-score   support

         0.0       0.85      0.88      0.86      2678
         1.0       0.71      0.58      0.64      1047
         2.0       0.71      0.84      0.77       407

    accuracy                           0.80      4132
   macro avg       0.76      0.77      0.76      4132
weighted avg       0.80      0.80      0.80      4132





              precision    recall  f1-score   support

         0.0       0.82      0.84      0.83      1149
         1.0       0.61      0.49      0.54       449
         2.0       0.57      0.75      0.65       174

    accuracy                           0.74      1772
   macro avg       0.67      0.69      0.67      1772
weighted avg       0.74      0.74      0.74      1772

Navie Bayes

In [199]:
clf4 =GaussianNB()
clf4 = clf4.fit(X_train, Y_train)
traine_pred_df = clf4.predict(X_train)
teste_pred_df = clf4.predict(X_test)
In [200]:
print(classification_report(Y_train, traine_pred))
print(classification_report(Y_test, teste_pred))
              precision    recall  f1-score   support

         0.0       0.85      0.88      0.86      2678
         1.0       0.71      0.58      0.64      1047
         2.0       0.71      0.84      0.77       407

    accuracy                           0.80      4132
   macro avg       0.76      0.77      0.76      4132
weighted avg       0.80      0.80      0.80      4132

              precision    recall  f1-score   support

         0.0       0.82      0.84      0.83      1149
         1.0       0.61      0.49      0.54       449
         2.0       0.57      0.75      0.65       174

    accuracy                           0.74      1772
   macro avg       0.67      0.69      0.67      1772
weighted avg       0.74      0.74      0.74      1772

AdaBoostClassifier

In [201]:
clf5=AdaBoostClassifier()
clf5 = clf5.fit(X_train, Y_train)
traine_pred_df = clf5.predict(X_train)
teste_pred_df = clf5.predict(X_test)
In [202]:
print(classification_report(Y_train, traine_pred))

print(classification_report(Y_test, teste_pred))
              precision    recall  f1-score   support

         0.0       0.85      0.88      0.86      2678
         1.0       0.71      0.58      0.64      1047
         2.0       0.71      0.84      0.77       407

    accuracy                           0.80      4132
   macro avg       0.76      0.77      0.76      4132
weighted avg       0.80      0.80      0.80      4132

              precision    recall  f1-score   support

         0.0       0.82      0.84      0.83      1149
         1.0       0.61      0.49      0.54       449
         2.0       0.57      0.75      0.65       174

    accuracy                           0.74      1772
   macro avg       0.67      0.69      0.67      1772
weighted avg       0.74      0.74      0.74      1772

In [203]:
test=test.drop('outage_duration',axis=1)
In [204]:
test.head()
Out[204]:
id area_code transit_server_other_0 transit_server_other_1 transit_server_other_2 transit_server_type_10 transit_server_type_11 transit_server_type_13 transit_server_type_14 transit_server_type_15 ... broadband_type_5 broadband_type_6 broadband_type_7 broadband_type_8 broadband_type_9 outage_type_1 outage_type_2 outage_type_3 outage_type_4 outage_type_5
5904 3340 342 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 1 0
5905 14067 872 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 1 0 0 0 0
5906 1134 171 0 0 0 0 1 0 0 0 ... 0 0 0 1 0 1 0 0 0 0
5907 27 734 0 0 0 0 1 0 0 0 ... 0 0 0 1 0 1 0 0 0 0
5908 9793 342 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 1 0 0 0

5 rows × 193 columns

In [205]:
test_pred_1=clf_3_cv.predict(test)
In [206]:
test_pred_1
Out[206]:
array([0., 0., 0., ..., 0., 0., 0.])
In [207]:
final_dt=pd.DataFrame({'id':test['id'],'outage_duration':test_pred_1})
In [208]:
final_dt.head()
Out[208]:
id outage_duration
5904 3340 0.0
5905 14067 0.0
5906 1134 0.0
5907 27 0.0
5908 9793 0.0
In [209]:
final_dt.reset_index()
Out[209]:
index id outage_duration
0 5904 3340 0.0
1 5905 14067 0.0
2 5906 1134 0.0
3 5907 27 0.0
4 5908 9793 0.0
... ... ... ...
1472 7376 7975 1.0
1473 7377 10664 0.0
1474 7378 7753 0.0
1475 7379 989 0.0
1476 7380 3129 0.0

1477 rows × 3 columns

In [210]:
final_dt.reset_index(drop=True)
Out[210]:
id outage_duration
0 3340 0.0
1 14067 0.0
2 1134 0.0
3 27 0.0
4 9793 0.0
... ... ...
1472 7975 1.0
1473 10664 0.0
1474 7753 0.0
1475 989 0.0
1476 3129 0.0

1477 rows × 2 columns

In [212]:
## grid search cv
final_dt.to_csv("C:/Users/admin/Desktop/Mohammed Final Exam//Submission_1_PHD.csv",index=False)
In [213]:
test_pred_2 = clf4.predict(test)
In [214]:
test_pred_2
Out[214]:
array([1., 1., 2., ..., 1., 1., 1.])
In [215]:
final_dt2=pd.DataFrame({'id':test['id'],'outage_duration':test_pred_2}) 
In [216]:
# GaussianNB
final_dt2.to_csv("C:/Users/admin/Desktop/Mohammed Final Exam//Submission_2_PHD.csv",index=False)

Conclusion

My best performed model is grid search cv, with F1 score 0.76 Train data and 0.67 on Test data.